﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Reflection;
using Dapper;
using Newtonsoft.Json;
using API_Service.DBHelper;

namespace API_Service.BizLogic.Material
{
    public class MaterialService : BaseLogicService
    {
        public ExecutionResult<dynamic> QueryMaterialKPNList(string vType = null)
        {
            ExecutionResult<dynamic> result;
            result = new ExecutionResult<dynamic>();
            #region  SQL and parameters
            DynamicParameters dbParams = new DynamicParameters();
            string sql = @"SELECT DISTINCT [id] ,[key_part_no]
                                  FROM [dbo].[c_material_t] WHERE 1=1";
            if (!string.IsNullOrEmpty(vType))
            {
                sql = sql + " AND [type] = @type";
                dbParams.Add("@type", vType, DbType.String, ParameterDirection.Input);
            }
            #endregion
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    result.Datas = conn.Query(sql, dbParams).ToArray();
                    result.Status = true;
                    result.Message = "查询物料料号信息成功.";
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "查询物料料号信息异常。异常信息:" + ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
            }

            return result;
            return result;
        }

        public ExecutionResult<dynamic> QueryMaterialInfo(string vKPN = null,string vType=null)
        {
            ExecutionResult<dynamic> result;
            result = new ExecutionResult<dynamic>();

            #region  SQL and parameters
            DynamicParameters dbParams = new DynamicParameters();
            string sql = @"SELECT [id]
                                      ,[key_part_no]
                                      ,[type]
                                      ,[description]
                                      ,[spec]
                                      ,[unit]
                                      ,[remarks]
                                      ,[erp_create_time]
                                      ,[erp_update_time]
                                      ,[is_valid]
                                      ,[create_time]
                                      ,[create_user]
                                      ,[update_time]
                                      ,[update_user]
                                  FROM [dbo].[c_material_t] WHERE 1=1";
            if (!string.IsNullOrEmpty(vKPN))
            {
                sql = sql + " AND [key_part_no] = @key_part_no";
                dbParams.Add("@key_part_no", vKPN, DbType.String, ParameterDirection.Input);
            }
            if (!string.IsNullOrEmpty(vType))
            {
                sql = sql + " AND [type] = @type";
                dbParams.Add("@type", vType, DbType.String, ParameterDirection.Input);
            }
            #endregion

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    result.Datas = conn.Query(sql, dbParams).ToArray();
                    result.Status = true;
                    result.Message = "查询物料信息成功.";
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "检查物料信息时异常。异常信息:" + ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
            }

            return result;
        }

        public ExecutionResult<dynamic> InesrtMaterialInfo(dynamic vModel)
        {
            ExecutionResult<dynamic> result;
            result = new ExecutionResult<dynamic>();
            var model = JsonConvert.DeserializeObject(vModel);
            if (model != null)
            {
                #region SQL and parameters
                string _sql = @"INSERT INTO [dbo].[c_material_t] ([key_part_no] ,[type] ,[description]  ,[spec]
                                                   ,[unit]  ,[remarks] ,[erp_create_time] ,[erp_update_time] ,[is_valid]
                                                   ,[create_time] ,[create_user])
                                        VALUES(@key_part_no,@type,@description,@spec,@unit,@remarks,@erp_create_time,
		                                           @erp_update_time,@is_valid, GETDATE(),@create_user)";
                DynamicParameters dbParams = new DynamicParameters();
                dbParams.Add("@type", model.type, DbType.String, ParameterDirection.Input);
                dbParams.Add("@description", model.description, DbType.String, ParameterDirection.Input);
                dbParams.Add("@spec", model.spec, DbType.String, ParameterDirection.Input);
                dbParams.Add("@remarks", model.remarks, DbType.String, ParameterDirection.Input);
                if (!String.IsNullOrEmpty(model.erp_create_time))
                {
                    dbParams.Add("@erp_create_time", model.erp_create_time, DbType.String, ParameterDirection.Input);
                }
                else
                {
                    dbParams.Add("@erp_create_time", model.erp_create_time, DbType.String, ParameterDirection.Input);
                }
                if (!String.IsNullOrEmpty(model.erp_update_time))
                {
                    dbParams.Add("@erp_update_time", "", DbType.String, ParameterDirection.Input);
                }
                else
                {
                    dbParams.Add("@erp_update_time", "", DbType.String, ParameterDirection.Input);
                }                
                dbParams.Add("@is_valid", model.is_valid, DbType.String, ParameterDirection.Input);
                dbParams.Add("@create_user", model.update_user, DbType.String, ParameterDirection.Input);
                #endregion
                using (IDbConnection conn = SqlCreator.Create())
                {
                    try
                    {
                        if (conn.State != ConnectionState.Open)
                            conn.Open();

                        result.ExecuteCount = conn.Execute(_sql, dbParams);
                        if (result.ExecuteCount > 0)
                        {
                            result.Status = true;
                            result.Message = "新增物料信息成功。";
                        }
                        else
                        {
                            result.Status = false;
                            result.Message = "新增物料信息失败。";
                        }
                        Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + "-->" + result.Message);
                    }
                    catch (Exception ex)
                    {
                        result.Status = false;
                        result.Message = "新增物料信息异常，异常信息:" + ex.Message;
                        Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                    }
                }
            }
            else
            {
                result.Status = false;
                result.Message = "新增物料时不能传递空的参数";
            }
            return result;
        }

        public ExecutionResult<dynamic> UpdateMaterialInfo(dynamic vModel)
        {
            ExecutionResult<dynamic> result;
            result = new ExecutionResult<dynamic>();
            var model = JsonConvert.DeserializeObject(vModel);
            if(model != null)
            {
                #region SQL and parameters
                string _sql = @"UPDATE [dbo].[c_material_t]
                                           SET [type] = @type,[description] = @description,[spec] = @spec,[unit] = @unit
                                                ,[remarks] = @remarks,[erp_create_time] = @erp_create_time
                                                ,[erp_update_time] = @erp_update_time,[is_valid] = @is_valid
                                                ,[update_time] = GETDATE()
                                                ,[update_user] = @update_user
                                         WHERE [id] = @id AND [key_part_no] =@key_part_no";
                DynamicParameters dbParams = new DynamicParameters();
                dbParams.Add("@type", model.type, DbType.String, ParameterDirection.Input);
                dbParams.Add("@description", model.description, DbType.String, ParameterDirection.Input);
                dbParams.Add("@spec", model.spec, DbType.String, ParameterDirection.Input);
                dbParams.Add("@remarks", model.remarks, DbType.String, ParameterDirection.Input);
                if (!String.IsNullOrEmpty(model.erp_create_time))
                {
                    dbParams.Add("@erp_create_time", model.erp_create_time, DbType.String, ParameterDirection.Input);
                }
                else
                {
                    dbParams.Add("@erp_create_time", model.erp_create_time, DbType.String, ParameterDirection.Input);
                }
                if (!String.IsNullOrEmpty(model.erp_update_time))
                {
                    dbParams.Add("@erp_update_time", "", DbType.String, ParameterDirection.Input);
                }
                else
                {
                    dbParams.Add("@erp_update_time", "", DbType.String, ParameterDirection.Input);
                }
                dbParams.Add("@is_valid", model.is_valid, DbType.String, ParameterDirection.Input);
                dbParams.Add("@update_user", model.update_user, DbType.String, ParameterDirection.Input);
                dbParams.Add("@id", model.id, DbType.Int32, ParameterDirection.Input);
                dbParams.Add("@key_part_no", model.key_part_no, DbType.String, ParameterDirection.Input);
                #endregion
                using (IDbConnection conn = SqlCreator.Create())
                {
                    try
                    {
                        if (conn.State != ConnectionState.Open)
                            conn.Open();

                        result.ExecuteCount = conn.Execute(_sql, dbParams);
                        if(result.ExecuteCount > 0)
                        {
                            result.Status = true;
                            result.Message = "更新物料信息成功。";                            
                        }
                        else
                        {
                            result.Status = false;
                            result.Message = "更新物料信息失败。";
                        }
                        Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + "-->" + result.Message);
                    }
                    catch (Exception ex)
                    {
                        result.Status = false;
                        result.Message = "更新物料信息异常，异常信息:" + ex.Message;
                        Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                    }
                }
            }
            else
            {
                result.Status = false;
                result.Message = "更新物料时不能传递空的参数";
            }

            return result;
        }

        public ExecutionResult<dynamic> DeleteMaterialInfo(int vId)
        {
            ExecutionResult<dynamic> result;
            result = new ExecutionResult<dynamic>();
            string sql = "DELETE FROM [dbo].[c_material_t] WHERE [id] = @id";
            using(IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    if (conn.State != ConnectionState.Open)
                        conn.Open();

                    result.ExecuteCount = conn.Execute(sql, new { id = vId });
                    result.Status = true;
                    result.Message = "删除物料信息成功。";

                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + "---" + result.Message);
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "删除物料信息异常，异常信息："+ex.Message;
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name, ex);
                }
            }

            return result;
        }

        public ExecutionResult<dynamic> CheckMaterial(string kpn)
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = "select * from c_material_t where key_part_no=@kpn";

                    conn.Open();

                    result.Datas = conn.Query(sql, new { kpn = kpn }).ToArray();

                    if (result.Datas.Length > 0)
                    {
                        result.Status = true;
                        result.Message = "OK";                        
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "物料号 " + kpn + " 不存在.";
                    }
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);
                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> GetMaxSeqNo(string kpn)
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = "select key_part_no,max(kpn_sn) seq from [dbo].[r_kpn_sn_t] where key_part_no=@kpn and print_date = '20181221' group by key_part_no";

                    conn.Open();

                    result.Datas = conn.Query(sql, new { kpn = kpn }).ToArray();

                    if (result.Datas.Length > 0)
                    {
                        result.Status = true;
                        result.Message = "OK";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "物料号 " + kpn + " 不存在.";
                    }
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);
                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }

    }
}